﻿#region Using directives

using System;
using System.Text;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
//using Metarnet.MVC.Framework;
#endregion

namespace MetarCommonSupport
{
    public class SqlAgent : IDBAgent,System.IDisposable 
    {
        private SqlConnection m_aConn;
        private SqlCommand m_aCmd;
        private SqlDataAdapter m_aAdpt;

		private SqlTransaction m_aTran;
		private bool m_OpenTransaction;

        public SqlAgent(string strConn)
        {
            this.m_aConn = new SqlConnection();
            this.m_aCmd = new SqlCommand();
            this.m_aAdpt = new SqlDataAdapter();

            m_aConn.ConnectionString = strConn;
            this.m_aCmd.Connection = this.m_aConn;
            this.m_aAdpt.SelectCommand = this.m_aCmd;

			this.m_OpenTransaction = false ;
        }

		//Modify by xhliu 060513 
        public int ExeNonQuery(string strSql)
        {
            try
            {
                this.m_aCmd.CommandText = strSql;
                if (this.m_aConn.State == ConnectionState.Closed)
                    this.m_aConn.Open();
				if( this.m_OpenTransaction )
					this.m_aCmd.Transaction = this.m_aTran ;
                int nReturn =  this.m_aCmd.ExecuteNonQuery();
				if( !this.m_OpenTransaction)
					this.m_aConn.Close();
                return nReturn;
            }
            catch
            {
                if (this.m_aConn.State == ConnectionState.Open && !this.m_OpenTransaction)
                    this.m_aConn.Close();
                throw;
            }
        }

        public DataTable ExeQuery(string strSql)
        {
            this.m_aCmd.CommandText = strSql;
            DataTable dtResult = new DataTable();
			try
			{
				this.m_aAdpt.Fill(dtResult);
			}
			catch(Exception ex)
			{
				string strErrorMsg = "执行SQL语句出错！"+Environment.NewLine;
				strErrorMsg += "具体错误信息："+ex.Message+Environment.NewLine;
				strErrorMsg += "SQL语句："+Environment.NewLine+strSql;
				throw new Exception(strErrorMsg);
			}
            return dtResult;
        }

        public object ExecuteScalar(string strSql)
        {
            this.m_aCmd.CommandText = strSql;
            object returnObj=null;
            lock (this)
            {
                try
                {
                    returnObj=m_aCmd.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    string strErrorMsg = "执行SQL语句出错！" + Environment.NewLine;
                    strErrorMsg += "具体错误信息：" + ex.Message + Environment.NewLine;
                    strErrorMsg += "SQL语句：" + Environment.NewLine + strSql;
                    throw new Exception(strErrorMsg);
                }
            }
            return returnObj;
        }

		public DataSet ExeQuery(string strSql,int start,int length,string table)
		{
			this.m_aCmd.CommandText = strSql;
			DataSet ds=new DataSet();
			this.m_aAdpt.Fill(ds,start,length,table);
			return ds;
		}

        public DataTable ExeQuery(string strSql, string[] strPara, string[] strParaValue)
        {
            DataTable dt = new DataTable();
            return dt;
        }
        public int ExeProcedure(string strProdureName, string[] strPara, string[] strParamValue)
        {
            return 0;
        }

		/// <summary>
		/// Create by xhliu 060513 
		/// Begins a database transaction.
		/// </summary>
		public void BeginTransaction()
		{
			if (this.m_aConn.State != ConnectionState.Open)
			{
				this.m_aConn.Open ();
			}
			this.m_aTran = this.m_aConn .BeginTransaction ();
			this.m_OpenTransaction = true ;

		}

		/// <summary>
		/// Create by xhliu 060513 
		/// Commits the database transaction.
		/// </summary>
		/// <remarks>
		/// Will close the connection.
		/// </remarks>
		public void CommitTransaction()
		{
			this.m_aTran.Commit();
			this.m_aTran.Dispose();
			this.m_aTran = null;
			if (this.m_aConn.State != ConnectionState.Closed)
			{
				this.m_aConn.Close ();
			}
			this.m_OpenTransaction = false ;
		}

		/// <summary>
		/// Create by xhliu 060513 
		/// Rolls back a transaction from a pending state.
		/// </summary>
		/// <remarks>
		/// Will close the connection.
		/// </remarks>
		public void RollBackTransaction()
		{
			this.m_aTran.Rollback();
			this.m_aTran.Dispose();
			this.m_aTran = null;
			if (this.m_aConn.State != ConnectionState.Closed)
			{
				this.m_aConn.Close ();
			}
			this.m_OpenTransaction = false ;
		}


	

		/// <summary>
		/// Create by xhliu 060513 
		/// Releasing, or resetting resources.
		/// </summary>
		public void Dispose()
		{
			if ( !this.m_OpenTransaction )
			{
				if (this.m_aConn .State != ConnectionState.Closed)
				{
					this.m_aConn.Close ();
				}
			}
			else
			{
				if (this.m_aConn.State != ConnectionState.Closed)
				{
					this.RollBackTransaction();
				}
			}
		}


        #region IDBAgent 成员


        public object ExeStoredProcedure(string StoreName, string[] strParam, string[] strParamValue, int[] strParamType)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        #endregion
    }
}
